﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using System.Data.Common;

namespace server.app
{
    #region DbType
    internal abstract class DbType
    {
        public abstract DbConnection NewConnection(string connStr);

        public abstract DbCommand NewCommand(DbConnection conn, DbTransaction txn, string sqlStr);

        public abstract DbParameter NewParameter(string name, object value);

        public abstract DbDataAdapter NewDataAdapter(DbCommand cmd);
    }

    internal class DbSqlserver : DbType
    {
        public override DbConnection NewConnection(string connStr)
        {
            return new SqlConnection(connStr);
        }

        public override DbCommand NewCommand(DbConnection conn, DbTransaction txn, string sqlStr)
        {
            return new SqlCommand(sqlStr, (SqlConnection)conn, txn != null ? (SqlTransaction)txn : null);
        }

        public override DbParameter NewParameter(string name, object value)
        {
            return new SqlParameter(name, value);
        }

        public override DbDataAdapter NewDataAdapter(DbCommand cmd)
        {
            return new SqlDataAdapter((SqlCommand)cmd);
        }
    }
    #endregion

    public class SQLxWhere
    {
        public string AllText { get; set; }
        public object[] AllArgs { get; set; }

        public string WhereText { get; set; }
        public object[] WhereArgs { get; set; }

        public string SetText { get; set; }
        public object[] SetArgs { get; set; }
    }

    public class SQLx
    {
        public static SQLxWhere FormatWhere(Dictionary<string, object> whereDic)
        {
            return FormatWhere(whereDic, new Dictionary<string, object>());
        }
        public static SQLxWhere FormatWhere(Dictionary<string, object> whereDic, Dictionary<string, object> setDic)
        {
            var where = new SQLxWhere();
            var allTxt = new StringBuilder();
            var allArgs = new List<object>();

            var index = 0;
            if (setDic.Count > 0)
            {
                var sb1 = new StringBuilder();
                foreach (var key in setDic.Keys)
                {
                    if (key == "fix")
                    {
                        sb1.Append(setDic[key] + " ,");
                    }
                    else if (setDic[key] != null)
                    {
                        sb1.Append(" " + key + "=|" + index + "| ,");
                        allArgs.Add(setDic[key]);
                        index++;
                    }
                }
                if (sb1.Length > 0)
                {
                    sb1.Insert(0, " set");
                    sb1.Remove(sb1.Length - 1, 1);
                }
                where.SetText = sb1.ToString();
                where.SetArgs = allArgs.ToArray();
                allTxt.Append(sb1.ToString());
            }
            if (whereDic.Count > 0)
            {
                var sb2 = new StringBuilder();
                foreach (var key in whereDic.Keys)
                {
                    if (whereDic[key] != null)
                    {
                        var h = "=";
                        if (key.EndsWith("=") || key.EndsWith("<>"))
                        {
                            h = "";
                        }
                        sb2.Append(" " + key + h + "|" + index + "| and");
                        allArgs.Add(whereDic[key]);
                        index++;
                    }
                }
                if (sb2.Length > 0)
                {
                    sb2.Insert(0, " where");
                    sb2.Remove(sb2.Length - 3, 3);
                }
                where.WhereText = sb2.ToString();
                where.WhereArgs = allArgs.ToArray();
                allTxt.Append(sb2.ToString());
            }

            where.AllText = allTxt.ToString();
            where.AllArgs = allArgs.ToArray();

            return where;
        }

        public static void Call(string dbType, string connStr, Action<SQLx> callback)
        {
            Call<string>(dbType, connStr, sqlx =>
            {
                callback(sqlx);
                return "";
            }, null);
        }
        public static T Call<T>(string dbType, string connStr, Func<SQLx, T> callback)
        {
            return Call<T>(dbType, connStr, callback, null);
        }
        public static T Call<T>(string dbType, string connStr, Func<SQLx, T> callback, Action<string> fail)
        {
            DbType _dbType = new DbSqlserver();

            var sqlStrList = new List<string>();
            try
            {
                using (var conn = _dbType.NewConnection(connStr))
                {
                    conn.Open();
                    return callback(new SQLx(conn, _dbType, sqlStrList));
                }
            }
            catch (Exception ex)
            {
                //if (fail != null)
                //{
                //    fail(ex.Message);
                //}
                var sb = new StringBuilder();
                foreach (var ss in sqlStrList)
                {
                    sb.Append(ss + ", ");
                }
                if (sb.Length > 0)
                {
                    sb.Remove(sb.Length - 2, 2);
                }
                Console.WriteLine(sb + "\r\n" + ex.Message + "\r\n" + ex.StackTrace);
                throw new Exception(sb + " | " + ex.Message + " | " + ex.StackTrace);
            }

            return default(T);
        }

        DbConnection _conn = null;
        DbTransaction _txn = null;
        List<string> _sqlStrList = null;
        DbType _dbType = null;

        DbCommand CreateCommand(string sqlStr, params object[] qs)
        {
            sqlStr = Regex.Replace(sqlStr, @"\|(\d+)\|", new MatchEvaluator(mh =>
            {
                return "@" + mh.Groups[1].Value + "";
            }));
            var cmd = _dbType.NewCommand(_conn, _txn, sqlStr);
            for (int i = 0, len = qs.Length; i < len; i++)
            {
                cmd.Parameters.Add(_dbType.NewParameter(i.ToString(), qs[i]));
            }
            _sqlStrList.Add(cmd.CommandText);
            return cmd;
        }

        internal SQLx()
        {
        }

        internal SQLx(DbConnection conn, DbType dbType, List<string> sqlStrList)
        {
            _conn = conn;
            _dbType = dbType;
            _sqlStrList = sqlStrList;
        }

        public void BeginTxn()
        {
            _txn = _conn.BeginTransaction();
        }
        public void RollbackTxn()
        {
            if (_txn == null)
            {
                throw new Exception("请先 Begin Txn");
            }
            _txn.Rollback();
        }
        public void CommitTxn()
        {
            if (_txn == null)
            {
                throw new Exception("请先 Begin Txn");
            }
            _txn.Commit();
        }
        public int ExecuteNonQuery(string sqlStr, params object[] qs)
        {
            using (var cmd = CreateCommand(sqlStr, qs))
            {
                return cmd.ExecuteNonQuery();
            }
        }
        public object ExecuteScalar(string sqlStr, params object[] qs)
        {
            using (var cmd = CreateCommand(sqlStr, qs))
            {
                return cmd.ExecuteScalar();
            }
        }
        public DataTable FillTable(string sqlStr, params object[] qs)
        {
            using (var cmd = CreateCommand(sqlStr, qs))
            {
                using (var da = _dbType.NewDataAdapter(cmd))
                {
                    var dt = new DataTable();
                    da.Fill(dt);
                    return dt;
                }
            }
        }
        public void ExecuteReader(string sqlStr, Action<DbDataReader> callback, params object[] qs)
        {
            ExecuteReader(sqlStr, dr =>
            {
                callback(dr);
                return true;
            }, qs);
        }
        public void ExecuteReader(string sqlStr, Func<DbDataReader, bool> callback, params object[] qs)
        {
            using (var cmd = CreateCommand(sqlStr, qs))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (!callback(reader))
                        {
                            break;
                        }
                    }
                }
            }
        }
        public bool TableExists(string tableName)
        {
            return (long)ExecuteScalar("select count(*) from sqlite_master where type=|0| and name=|1|", "table", tableName) > 0;
        }

    }
}
